
package com.huizhan.web.dao;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import java.util.List;
import com.huizhan.web.model.*;

@Repository 
public interface TicketDao extends JpaRepository<Ticket,Long> 
{
    /**
     * 根据项目id获取取票数量
     * @param projectId  项目id
     * @return 数量
     */
     Integer countByProjectIdAndCodeAndIsPassage(String projectId,String code,Integer isPassage);
   
    /**
     * 根据项目id获取取票数量
     * @param projectId  项目id
     * @return 数量
     */
    Integer countByTicketTypeIdAndCode(String projectId,String code);
   
    /**
     * 根据项目id获取取票
     * @param projectId  项目id
     * @return 数量
     */
    Ticket findByTicketTypeIdAndCode(Long ticketTypeId,String code);
   
    /**
     * 根据项目id获取取票
     * @param projectId  项目id
     * @return 数量
     */
    @Query(value = "SELECT * FROM ticket WHERE ticket_type_id = ?1 AND `code`= ?2 and is_passage = 0",nativeQuery = true)
    List<Ticket> findByWhere(Long ticketTypeId,String code);
   
    /**
     * 
     * @param projectId
     * @param code
     * @return
     */
    Ticket findByProjectIdAndIdNumber(String projectId,String idNumber);


    /**
     * 根据项目id获取票数量
     * @param projectId  项目id
     * @return 数量
     */
     Integer countByProjectId(String projectId);
   
    /**
     * 根据类型id获取票数量
     * @param projectId  类型id
     * @return 数量
     */
     Integer countByTicketTypeId(Long ticketTypeId);
   
     /**
      * 
      * @param ticketTypeId
      * @return
      */
     List<Ticket> findByTicketTypeId(Long ticketTypeId);

     /**
      * 
      * @param orderItemId
      * @return
      */
      List<Ticket> findByOrderItemId(Long orderItemId);

     /**
      * 根据类型id和名称获取票
      * @param ticketTypeId
      * @param name
      * @return
      */
     List<Ticket> findByTicketTypeIdAndNameAndIsPassage(Long ticketTypeId,String name,Integer isPassage);

     /**
      * 
      * @param ticketTypeId
      * @param code
      * @return
      */
     Integer countByTicketTypeIdAndCode(Long ticketTypeId,String code);

     /**
      * 根据票记录删除
      * @param ticketRecordId
      */
        @Transactional
        Integer deleteByTicketRecordId(Long ticketRecordId);

     /**
      * 根据票记录删除
      * @param ticketRecordId
      */
      @Transactional
      Integer deleteByTicketComplimentaryId(Long ticketComplimentaryId);

     
    /**
     * 分页查赠票记录集合
     * @param name 名称
     * @param phone 手机号
     * @param ticket_Type_Id 票类型ID
     * @param project_Id 项目ID
     * @param ticket_complimentary_Id 类型
     * @param pageable 分页参数
     * @return 赠票记录集合
     */
    @Query(
        value = "SELECT * FROM ticket"
            + " WHERE (project_Id =?3 ) "
            + " AND (is_Passage = ?6 OR ?6  = -1 )"
            + " AND (ticket_Type_Id =?4 OR ?4 = 0 )"
            + " AND (id_number =?5 OR ?5 = '')"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )", 
        countQuery = "SELECT COUNT(1) FROM ticket"
            + " WHERE (project_Id =?3 ) "
            + " AND (is_Passage = ?6 OR ?6  = -1 )"
            + " AND (ticket_Type_Id =?4 OR ?4 = 0 )"
            + " AND (id_number =?5 OR ?5 = '')"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )", 
        nativeQuery = true
    )
    Page<Ticket> findByAll(String name,String code,String project_Id,Long ticket_Type_Id,String idNumber,Integer isPassage, Pageable  pageable);

    /**
     * 分页查赠票记录集合
     * @param ticket_complimentary_Id 类型
     * @param pageable 分页参数
     * @return 赠票记录集合
     */
    @Query(
        value = "SELECT * FROM ticket WHERE (ticket_complimentary_Id =?1 )", 
        countQuery = "SELECT COUNT(1) FROM ticket WHERE  (ticket_complimentary_Id =?1 )",
        nativeQuery = true
    )
    Page<Ticket> findByWhere(Long ticket_complimentary_Id, Pageable  pageable);
}
